/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pruebas;

import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Vector;
import org.json.simple.*;
import org.postgresql.geometric.PGpoint;
import org.postgresql.geometric.PGpolygon;




/**
 *
 * @author jw0rmc
 */
public class pgConnect {
    private static Map<Integer,PGpolygon> poligonos;
    private static Vector<Latlon> latlons;; 
    
    public static void conn(){
		System.out.println("-------- PostgreSQL JDBC coneccion ------------");
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("JDBC Driver ???? onta!");
			e.printStackTrace();
			return;
		}
		System.out.println("PostgreSQL JDBC Driver ok");
		Connection connection = null;
		try {
                connection = DriverManager.getConnection("jdbc:postgresql://192.168.0.109:5432/desarr", "moises2","sigem");
      Statement select = connection.createStatement();
      ResultSet result = select.executeQuery("SELECT id,astext(geowkt) FROM gua_gto2_pred");
      PGpolygon val;
      poligonos=new HashMap<Integer, PGpolygon>();
      System.out.println("Query results:");
      
      while (result.next()) { // process results one row at a time
       int key = result.getInt(1);
     // System.out.println(result.getObject(2).toString().substring(7));
      // System.out.println("-----------------------------------------------------------------------------");
       val = procesadoraDeLacteos(result.getObject(2).toString().substring(7));
       
       poligonos.put(key,val);
      }
     
		} catch (SQLException e) {
			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return;
		}
 
		if (connection != null) {
			System.out.println("Coneccion lista!");
		} else {
			System.out.println("Falla Conexion!");
		}
}  
    
    
    public static PGpolygon procesadoraDeLacteos(String s){
        String ss=s;
        
        Vector<String> v=new Vector<String>();
        int index=0;
        ss=ss.replace('(', ' ').replace(')',' ');
        ss=ss.trim();
        String ss2=ss;
        for(int ch=0;ch<ss.length();ch++)
        {
        if(ss.charAt(ch) == ','){
           v.add(ss2.substring(index,ch).replace(',', ' ').trim());     
           index=ch;
        }
        
        if(ch==ss.length()-1)
            {
           v.add(ss2.substring(index,ss.length()).replace(',', ' ').trim());     
        }
        }
        
        
        Vector<Latlon> vLatLon=new Vector<Latlon>();
        int index2=0;
        for(int i=0;i<v.size();i++)
        for(int ch=0;ch<v.get(i).length();ch++)
              if(v.get(i).charAt(ch) == ' '){
                  vLatLon.add(new Latlon(v.get(i).substring(index2,ch),v.get(i).substring(ch+1,v.get(i).length())));   
              }
        
        
     //   for(int ch=0;ch<vLatLon.size();ch++)
       //         System.out.println("  " + vLatLon.get(ch).getLatS()   +  "  |  "  +  vLatLon.get(ch).getLonS());
        
        
        
        PGpoint puntos[]=new PGpoint[vLatLon.size()];
        for(int p=0;p<puntos.length;p++){
            puntos[p]=new PGpoint(vLatLon.get(p).getLat(),vLatLon.get(p).getLon());
        }
        
        PGpolygon poligonsin=new PGpolygon(puntos);
        
     return poligonsin;   
    }
    
    public static void main(String args[]){
        conn();
       // System.out.println(getGeoJsonCodificacion(llenaFeaturesJSon()));
        
        try{
            
            
            FileWriter outFile=new FileWriter("web/pruebaconids.json");
            PrintWriter out=new PrintWriter(outFile);
            out.println(getGeoJsonCodificacion(llenaFeaturesJSon()));
            out.close();
            
        }catch(IOException e){}
        
        

    }
    
    
    public static Vector<Map> llenaFeaturesJSon(){
    Vector<Map> v=new Vector<Map>();    
    
    //se crean los poligonos que hay 
    for(int polis=0;polis<poligonos.size();polis++)
    v.add(new LinkedHashMap());
    
    
   //while poligonos next,   itere la key
    Iterator itera2=poligonos.keySet().iterator();
    int polis=0;
        while(itera2.hasNext()){
            int poligonId=(Integer)itera2.next();
    v.get(polis).put("type","Feature");
    v.get(polis).put("id","OpenLayers.Feature.Vector_1489");
    v.get(polis).put("properties",new JSONObject());
    latlons=new Vector<Latlon>();
    
    
    //agrega puntos del poligono al formato
    for(int pts=0;pts<poligonos.get(poligonId).points.length;pts++){
        latlons.add(new Latlon(poligonos.get(poligonId).points[pts].x,poligonos.get(poligonId).points[pts].y));
    }
    
    
    v.get(polis).put("geometry",getObjGeometryJson(latlons));
    v.get(polis).put("crs",getObjCRS());
   
    polis++;
    }
    
    /*
      Iterator itera2=poligonos.keySet().iterator();
        while(itera2.hasNext()){
        System.out.println(""+(Integer)itera2.next());
        }*/
    
    return v;
    }
    
    
    
    
     public static Vector<Map> llenaFeaturesJSonBackUp(){
    Vector<Map> v=new Vector<Map>();    
    
    //se crean los poligonos que hay 
    for(int polis=0;polis<poligonos.size();polis++)
    v.add(new LinkedHashMap());
    
    
   //while poligonos next,   itere la key
    for(int polis=0;polis<poligonos.size();polis++){
    v.get(polis).put("type","Feature");
    v.get(polis).put("id","OpenLayers.Feature.Vector_1489");
    v.get(polis).put("properties",new JSONObject());
    latlons=new Vector<Latlon>();
    
    for(int pts=0;pts<poligonos.get(polis+1).points.length;pts++){
        latlons.add(new Latlon(poligonos.get(polis+1).points[pts].x,poligonos.get(polis+1).points[pts].y));
    }
    
    
    v.get(polis).put("geometry",getObjGeometryJson(latlons));
    v.get(polis).put("crs",getObjCRS());
   
    }
    
     /*  Iterator itera2=poligonos.keySet().iterator();
        while(itera2.hasNext()){
        System.out.println(""+(Integer)itera2.next());
        }*/
    
    return v;
    }
    
public static String getGeoJsonCodificacion(Vector<Map> vv){
 
    
    
    /*
    Vector<Latlon> v=new Vector<Latlon>();
    v.add(new Latlon(-19.6875, 63.632812));
    v.add(new Latlon(-112.5, 35.5078125));
    v.add(new Latlon(-85.078125, 34.8046875));
    v.add(new Latlon(-68.90625, 39.7265625));
    v.add(new Latlon(-68.203125, 67.1484375));
    v.add(new Latlon(-109.6875, 63.6328125));
    
      Vector<Latlon> v2=new Vector<Latlon>();
    v2.add(new Latlon(-40.78125, 5.0390625));
    v2.add(new Latlon(-40.078125, 34.8046875));
    v2.add(new Latlon(-12.65625, 25.6640625));
    v2.add(new Latlon(21.09375, 17.2265625));
    v2.add(new Latlon(22.5, 58.0078125));
    v2.add(new Latlon(-40.78125, 65.0390625));
    
      Vector<Latlon> v3=new Vector<Latlon>();
    v3.add(new Latlon(-10.78125, 4.0390625));
    v3.add(new Latlon(-20.078125, 20.8046875));
    v3.add(new Latlon(-10.65625, 22.6640625));
    v3.add(new Latlon(18.09375, 15.2265625));
    v3.add(new Latlon(20.5, 58.0078125));
    v3.add(new Latlon(-30.78125, 55.0390625));
  
    
    
   JSON feature 1
  Map obj1=new LinkedHashMap();
  obj1.put("type","Feature");
  obj1.put("id","OpenLayers.Feature.Vector_1489");
  obj1.put("properties",new JSONObject());
  obj1.put("geometry",getObjGeometryJson(v));  
  obj1.put("crs",getObjCRS());

  
  JSON feature 2
  Map obj2=new LinkedHashMap();
  obj2.put("type","Feature");  
  obj2.put("id","OpenLayers.Feature.Vector_1668");
  obj2.put("properties",new JSONObject());
  obj2.put("geometry",getObjGeometryJson(v2));
  obj2.put("crs",getObjCRS());
  
   JSON feature 3
  Map obj3=new LinkedHashMap();
  obj3.put("type","Feature");  
  obj3.put("id","OpenLayers.Feature.Vector_1669");
  obj3.put("properties",new JSONObject());
  obj3.put("geometry",getObjGeometryJson(v3));
  obj3.put("crs",getObjCRS());
  
  
  
   ARRAY JSON  featuress ( total de descripciones de poligonos )
  JSONArray list = new JSONArray();
  list.add(obj1);
  list.add(obj2);
 list.add(obj3);
  System.out.println(list);
*/
  
  //Despues de eter el nuevo code
  JSONArray list = new JSONArray();
  for(int i=0;i<vv.size();i++){
      list.add(vv.get(i));
  }
  
  
  
  Map obj=new LinkedHashMap();
  obj.put("type","FeatureCollection");
  obj.put("features",list);
  String jsonText = JSONValue.toJSONString(obj);
 return jsonText;
}

static public Map getObjCRS(){
 //crs OBJECT
   Map objCRSProperties=new LinkedHashMap();
  objCRSProperties.put("urn","urn:ogc:def:crs:OGC:1.3:CRS84");
  
  Map objCRS=new LinkedHashMap();
  objCRS.put("type","OGC");
  objCRS.put("properties",objCRSProperties);
    
  return objCRS;
}


static public Map getObjGeometryJson(Vector<Latlon> vv){
 //lista COORDENADASSSNNNN Lat Lon
    JSONArray listLatLon = new JSONArray(); 
    listLatLon.add(getListLatLon(vv));  //lista con todas las coords
   //System.out.println(listLatLon);
    
    //JSON GEOMETRY 1 OBJETO
     Map objG=new LinkedHashMap();
  objG.put("type","Polygon");
  objG.put("coordinates",listLatLon);
    
  return objG;
}

static public JSONArray getListLatLon(Vector<Latlon> vv){
  Vector<Latlon> v=vv;
    
    JSONArray coord=new JSONArray();
    JSONArray listLatLon2 = new JSONArray();   
    for(int ii=0;ii<v.size();ii++){
        coord.add(v.get(ii).getLat());
        coord.add(v.get(ii).getLon());
        listLatLon2.add(coord);
    //coord.clear();  si usas este, el valor agregado a listlatlon2 tbn se vacia
    coord=new JSONArray();
    }
    
    return listLatLon2;
}
 

}
class Latlon{
private Double lat;
private Double lon;
private String latS;
private String lonS;
    
public Latlon(Double lat,Double lon){
this.lat=lat;
this.lon=lon;
    
}

public Latlon(String lat,String lon){
this.latS=lat;
this.lonS=lon;

this.lat = Double.parseDouble(this.latS);
this.lon = Double.parseDouble(this.lonS);
    
}

public Double getLat(){

    return this.lat;
}


public Double getLon(){

    return this.lon;
}

public String getLatS(){

    return this.latS;
}


public String getLonS(){

    return this.lonS;
}

}

